Download Administering Relational Databases on Microsoft Azure.DP-300.Dump4Pass.2024-11-05.107q.vcex

CE: Estimate the vCores needed for the pool as follows: For vCore-based purchasing model: MAX(<Total number of DBs X average vCore utilization per DB>, <Number of concurrently peaking DBs X Peak vCore utilization per DB)  A: Estimate the storage space needed for the pool by adding the number of bytes needed for all the databases in the pool. Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overviewconfirmed

Step 1: Attach the SSISDB database   Step 2: Turn on the TRUSTWORTHY property and the CLR property If you are restoring the SSISDB database to an SQL Server instance where the SSISDB catalog was never created, enable common language runtime (clr)    Step 3: Open the master key for the SSISDB database Restore the master key by this method if you have the original password that was used to create SSISDB.    open master key decryption by password = 'LS1Setup!' --'Password used when creating SSISDB'  Alter Master Key Add encryption by Service Master Key  Step 4: Encrypt a copy of the mater key by using the service master key Reference: https://docs.microsoft.com/en-us/sql/integration-services/backup-restore-and-move-the-ssis-catalog

Columnstore tables and indexes are always stored with columnstore compression. You can further reduce the size of columnstore data by configuring an additional compression called archival compression.    Note:Columnstore—Thecolumnstoreindexisalsologicallyorganizedasatablewithrowsandcolumns,butthedataisphysicallystoredinacolumn-wisedata format.   Incorrect Answers: B: Rowstore — The rowstore index is the traditional style that has been around since the initial release of SQL Server. For rowstore tables and indexes, use the data compression feature to help reduce the size of the database.  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression

This table lists the key features for PolyBase and the products in which they're available.            Incorrect Answers: C: Change tracking is a lightweight solution that provides an efficient change tracking mechanism for applications. It applies to both Azure SQL Database and SQL Server.    D: Azure SQL Database and Azure SQL Managed Instance automatic tuning provides peak performance and stable workloads through continuous performance tuning based on AI and machine learning.    Reference: https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-versioned-feature-summary

Box 1: Full and log backups only Make sure to take every backup on a separate backup media (backup files). Azure Database Migration Service doesn't support backups that are appended to a single backup file. Take full backup and log backups to separate backup files.    Box 2: WITH CHECKSUM Azure Database Migration Service uses the backup and restore method to migrate your on-premises databases to SQL Managed Instance. Azure Database Migration Service only supports backups created using checksum.    Incorrect Answers:   NOINIT Indicates that the backup set is appended to the specified media set, preserving existing backup sets. If a media password is defined for the media set, the password must be supplied. NOINIT is the default.  UNLOAD  Specifies that the tape is automatically rewound and unloaded when the backup is finished. UNLOAD is the default when a session begins.  Reference: https://docs.microsoft.com/en-us/azure/dms/known-issues-azure-sql-db-managed-instance-onlineconfimred

confirmed

Box 1: blob The following example creates an external data source for Azure Data Lake Gen2  CREATE EXTERNAL DATA SOURCE YellowTaxi  WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/', TYPE = HADOOP)    Box 2: HADOOP   Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tablescorrected

Box 1: .partitionBy Example: df.write.partitionBy("y","m","d")    .mode(SaveMode.Append)    .parquet("/data/hive/warehouse/db_name.db/" +  tableName) Box 2: ("Year","Month","Day","Hour","StoreID") Box 3: .parquet("/Purchases")   Reference: https://intellipaat.com/community/11744/how-to-partition-and-write-dataframe-in-spark-without-deleting-partitions-with-no-new-data

Box 1: CREATE EXTERNAL TABLE An external table points to data located in Hadoop, Azure Storage blob, or AzureDataLakeStorage. External tables are used to read data from files or write data to files in Azure Storage. With SynapseSQL, you can use external tables to read external data using dedicated SQL pool or serverless SQL pool.    Syntax: CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }  ( <column_definition> [ ,...n ] )  WITH (  LOCATION = 'folder_or_filepath', DATA_SOURCE  = external_data_source_name, FILE_FORMAT =  external_file_format_name    Box 2. OPENROWSET  When using serverless SQL pool, CETAS is used to create an external table and export query results to Azure Storage Blob or Azure Data Lake Storage Gen2.   Exam ple: AS    SELECT decennialTime, stateName, SUM(population) AS population FROM  OPENROWSET(BULK 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=*/*.parquet', FORMAT='PARQUET') AS [r]  GROUP BY decennialTime, stateName  GO   Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables